﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Security.Principal
Imports System.Text
Imports System.IO
Imports System.Data.Odbc
Public Class frmDiesel
    Private admComercial As New AdminPAQ_Comercial
    Private mdsDatos As DataSet
    Dim idselect As Integer
    Private mstrValor As String
    Private mstrCondicion As String
    Dim ocurrioError As Boolean
    Dim Diesel As New clsDiesel
    Dim actual As Integer
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal

    Private minuto As Integer = 0
    Private segundo As Integer = 0
    Private milisegundo As Integer = 0

    Dim Continuar As Boolean

    Private NoEconomicos As New DataTable("NoEconomicos")
    Private AutocompletadoHorarios As New AutoCompleteStringCollection


    Public ReadOnly Property Valor() As String
        Get
            Valor = mstrValor
        End Get
    End Property

    Public WriteOnly Property Condicion() As String
        Set(ByVal value As String)
            mstrCondicion = value
        End Set
    End Property
    Private Sub frmDiesel_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al Módulo de Diesel")
        Usuario.Ubicacion("Ventana Principal")

        NoEconomicos.PrimaryKey = Nothing
        NoEconomicos.Clear()
        NoEconomicos.Columns.Clear()


    End Sub
    Private Sub frmDiesel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System
        ToolStrip1.Stretch = True
        'registra actividad
        Bitacora.RegistraActividad("Ingresó al Módulo de Diesel")
        Usuario.Ubicacion("Módulo de Diesel")

        ocurrioError = False

        cargaAutobuses()
        Dim Claves(0) As DataColumn
        Claves(0) = NoEconomicos.Columns("NoEconomicos")
        NoEconomicos.PrimaryKey = Claves

        For Each NoEconomico In NoEconomicos.Rows
            AutocompletadoHorarios.Add(NoEconomico("NoEconomicos"))
        Next

        RutaDiesel = ""
        BusDiesel = ""
        actual = 0

    End Sub
    Private Sub ControlEdicion(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles dgImportar.EditingControlShowing
        If dgImportar.CurrentCell.ColumnIndex <> 2 Then
            DirectCast(e.Control, TextBox).AutoCompleteMode = Nothing
        End If
        If dgImportar.CurrentCell.ColumnIndex = 2 Then
            DirectCast(e.Control, TextBox).AutoCompleteMode = AutoCompleteMode.SuggestAppend
            DirectCast(e.Control, TextBox).AutoCompleteSource = AutoCompleteSource.CustomSource
            DirectCast(e.Control, TextBox).AutoCompleteCustomSource = AutocompletadoHorarios
        End If

    End Sub

    Sub cargaAutobuses()

        NoEconomicos.Columns.Add("NoEconomicos")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "select NoEconomico from Autobuses where Estatus=1 order by NoEconomico"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim primero As Boolean
        primero = True

        Do While rdBuscar.Read()
            NoEconomicos.Rows.Add(rdBuscar("NoEconomico"))
        Loop

    End Sub

    Private Sub ToolStripButton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim resp As Integer = MsgBox("Deseas salir del Modulo de Diesel", MsgBoxStyle.YesNo, "Aviso")
        If resp = 6 Then
            Me.Close()
        End If
    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If ocurrioError = False Then
            MsgBox("La tabla se ha actualizado", MsgBoxStyle.Information, "Aviso")
        End If

    End Sub

    Sub Importar()
        If DiaTrabajadoDiesel(datFecha.Value) = True Then
            MsgBox("El dia " & datFecha.Text & " ya ha sido archivado", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        'COMIENZA EL CICLO PARA EL GUARDADO DE LA INFORMACION
        Dim ImporteAux As Double = 0

        Dim RutaDLL As String = ""
        Dim cCodigoDie As String = ""
        Dim UltimoOperador As String = ""

        Dim IdProducto As Integer = 0
        Dim IdAlmacen As Integer = 0
        Dim Costo As Double = 0
        Dim Fecha As String = datFecha.Value.ToString("MM/dd/yyyy")
        Dim strProducto As String = ""

        '*********************************************************************************************************************************************************************

        Try

            For i = 0 To dgImportar.Rows.Count - 1
                'If dgImportar.Rows(i).Cells(0).EditedFormattedValue = True Then
                dgImportar.CurrentCell = dgImportar.Rows(i).Cells(1)
                Application.DoEvents()

                'AHORA SE GUARDA EL DIESEL EN TABLA DE DIESEL
                Diesel.IdTanque = 0
                Diesel.NoCarga = 0
                Diesel.Fecha = datFecha.Value
                Diesel.Hora = Now()
                Diesel.Cuentalitros = 0
                Diesel.LitrosDisponibles = 0
                Diesel.Kardex = 0
                Diesel.Precio = 0
                Diesel.LitrosSurtidos = 0
                Diesel.IdAutobus = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE cCodigoC01='" & dgImportar.Rows(i).Cells(1).Value.ToString & "'")
                Diesel.NoEconomico = dgImportar.Rows(i).Cells(2).Value.ToString
                Diesel.IdRuta = BuscarIntDatoCitra("SELECT IdRuta FROM Autobuses WHERE IdAutobus=" & Diesel.IdAutobus)
                Diesel.NombreRuta = BuscarStrDatoCitra("SELECT Nombre FROM Rutas WHERE IdRuta=" & Diesel.IdRuta)
                Diesel.Litros = dgImportar.Rows(i).Cells(5).Value
                Diesel.KilometrosTotales = dgImportar.Rows(i).Cells(6).Value
                Diesel.KilometrosOperacion = dgImportar.Rows(i).Cells(6).Value
                Diesel.KilometrosMuertos = 0
                Diesel.RendimientoStandart = BuscarDblDatoCitra("SELECT Importe02 FROM Autobuses WHERE NoEconomico='" & Diesel.NoEconomico & "'")
                Diesel.RendimientoAnterior = 0
                Diesel.RendimientoDia = dgImportar.Rows(i).Cells(8).Value

                strProducto = BuscarStrDatoCitra("SELECT Texto03 FROM Rutas WHERE IdRuta=" & Diesel.IdRuta)
                IdProducto = BuscarIntDatoCitra("SELECT cIdProdu01 FROM MGW10005 WHERE cCodigoP01='" & strProducto & "'")
                Costo = BuscarDblDatoCitra("SELECT cUltimoC01 FROM MGW10017 WHERE cIdAlmacen=" & IdAlmacen & " AND cIdProdu01=" & IdProducto & " ORDER BY cIdCostoH DESC")

                Diesel.Subtotal = Costo * Diesel.Litros
                Diesel.IVA = Diesel.Subtotal * 0.16
                Diesel.Total = Diesel.Subtotal + Diesel.IVA

                UltimoOperador = BuscarStrDatoCitra("SELECT CodigoOperador FROM Liquidacion Where IdAutobus=" & Diesel.IdAutobus & " AND CONVERT(VARCHAR,FECHA,101)='" & Fecha & "'")
                If UltimoOperador <> "" Then
                    Diesel.CodigoOperador = UltimoOperador
                    Diesel.NombreOperador = BuscarStrDatoCitra("SELECT Nombre FROM OperadoresGlobal WHERE Codigo='" & UltimoOperador & "'")
                Else
                    Diesel.CodigoOperador = ""
                    Diesel.NombreOperador = ""
                End If


                Diesel.CodigoDespachador = ""
                Diesel.NombreDespachador = ""
                ImporteAux = dgImportar.Rows(i).Cells(6).Value.ToString() ' Trim(Str(Range("D" & Trim(Str(lRenglon))).Value))
                If ImporteAux = 0 Then
                    Diesel.Importe01 = 0
                ElseIf ImporteAux <= 350 And ImporteAux > 0 Then
                    Diesel.Importe01 = 2
                ElseIf ImporteAux > 350 And ImporteAux <= 700 Then
                    Diesel.Importe01 = 3
                ElseIf ImporteAux > 700 Then
                    Diesel.Importe01 = 4
                End If
                Diesel.Importe02 = 0
                Diesel.Importe03 = 0
                Diesel.Importe04 = 0
                Diesel.Importe05 = 0
                Diesel.Texto01 = ""
                Diesel.Texto02 = ""
                Diesel.Texto03 = ""
                Diesel.Texto04 = ""
                Diesel.Texto05 = ""
                Diesel.Insertar()

                'ACTUALIZA KILOMETRAJE DEL AUTOBUS
                Call SetSqlCitra("UPDATE Autobuses SET Importe01=" & dgImportar.Rows(i).Cells(4).Value & ",Texto01='" & Fecha & "' WHERE NoEconomico='" & Diesel.NoEconomico & "'")
                ' End If
            Next

        Catch ex As Exception

        End Try

        dgImportar.Rows.Clear()

        '*********************************************************************************************************************************************************************
        'Dim respuesta As Integer
        'respuesta = MsgBox("¿Desea actualizar los recorridos?", MsgBoxStyle.YesNo, "Aviso")
        'If respuesta = 6 Then
        'frmActualizar.ShowDialog()
        'End If

        Bitacora.RegistraActividad("Registro diesel del dia" & datFecha.Value)
    End Sub

    Private Sub AbastecimientosToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub
    Private Sub RendimientosToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oFD As New OpenFileDialog
        With oFD
            .Title = "Seleccionar fichero"
            .Filter = "Ficheros de texto (*.xls;*.xlsx)|*.xls;*.xlsx" & _
                      "|Todos los ficheros (*.*)|*.*"
            .FileName = Me.txtFile.Text
            If .ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Me.txtFile.Text = .FileName
            End If
        End With
        Excel()
        MsgBox("Ahora puede importar el archivo", MsgBoxStyle.Information, "Aviso")
    End Sub
    Private Sub Excel()

        dgImportar.Rows.Clear()
        Application.DoEvents()

        Dim xl As Object
        Dim wb As Object

        'Crear objeto excel
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        wb = xl.activeworkbook
        wb = xl.Workbooks.Open _
        (txtFile.Text, True, True, , "")

        Dim rng As Integer = 1
        Dim col As Integer = 1
        Dim i As Integer = 1

        Dim Interno As String = ""
        Dim Final As Double = 0
        Dim Litros As Double = 0
        Dim J As Integer = 0
        Dim rend As Double
        Dim odometro As Double = 0
        Dim aux As Integer = 0
        Dim idBus As Integer = 0
        Dim Bus As String = ""
        Dim Recorridos As Double = 0
        Dim Rendimiento As Double = 0
        Dim Diferencia As Double = 0
        Dim DieCon As String = ""
        Dim DieAlm As String = ""
        Dim DiePro As String = ""
        Dim KmsCon As String = ""
        Dim KmsAlm As String = ""
        Dim KmsPro As String = ""

        For i = 2 To 300
            Interno = wb.sheets(1).cells(i, 1).value

            Final = wb.sheets(1).cells(i, 4).value
            Litros = wb.sheets(1).cells(i, 5).value
            DieCon = wb.sheets(1).cells(i, 6).value
            DieAlm = wb.sheets(1).cells(i, 7).value
            DiePro = wb.sheets(1).cells(i, 8).value
            KmsCon = wb.sheets(1).cells(i, 9).value
            KmsAlm = wb.sheets(1).cells(i, 10).value
            KmsPro = wb.sheets(1).cells(i, 11).value

            If Interno = "" Then
                Exit For
            End If
            idBus = BuscarIntDatoCitra("Autobuses", "IdAutobus", "CCODIGOC01='" & Interno & "'")
            Bus = BuscarStrDatoCitra("Autobuses", "NoEconomico", "CIDCLIEN01=" & idBus)
            odometro = BuscarDblDatoCitra("Autobuses", "Importe01", "idautobus=" & idBus)
            rend = BuscarDblDatoCitra("SELECT Importe02 FROM Autobuses WHERE NoEconomico='" & Bus & "'")
            Recorridos = Final - odometro
            Rendimiento = Recorridos / Litros
            Diferencia = Rendimiento - rend
            dgImportar.Rows.Add(True, Interno, Bus, odometro, Final, Litros, Recorridos, rend, Rendimiento, Diferencia, DieCon, DieAlm, DiePro, KmsCon, KmsAlm, KmsPro)
            If odometro = 0 Then
                aux = aux + 1
            End If
            If Diferencia < 0 Then
                For J = 0 To 8
                    dgImportar.CurrentRow.Cells(J).Style.BackColor = Drawing.Color.Red
                Next
            End If
            Application.DoEvents()
        Next

        'cerramos el archivo Excel
        wb.Close(SaveChanges:=False)
        xl.Quit()
    End Sub
    Private Sub FinDeEdicion(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles dgImportar.CellEndEdit
        'Añadir aqui las validaciones necesarias

        Dim Final As Double = 0
        Dim Litros As Double = 0
        Dim rend As Double
        Dim odometro As Double = 0
        Dim Recorridos As Double = 0
        Dim Rendimiento As Double = 0
        Dim Diferencia As Double = 0

        Try
            'dgImportar.Rows(e.RowIndex).Cells(1).EditedFormattedValue = True

            If dgImportar.CurrentCell.ColumnIndex = 2 Then
                'Dim _Producto As DataRow = NombresProductos.Rows.Find(Me.dgProductos(e.ColumnIndex, e.RowIndex).Value)
                'Me.dgProductos(0, e.RowIndex).Value = _Producto("Nombre")
                'dgProductos.CurrentRow.Cells(1).Value = BuscarStrDatoCitra("MGW10005", "CNOMBREP01", "CCODIGOP01='" & dgProductos.CurrentRow.Cells(0).Value.ToString.Trim & "'")
                dgImportar.Rows(e.RowIndex).Cells(1).Value = BuscarStrDatoCitra("SELECT cCodigoC01 FROM Autobuses WHERE NoEconomico='" & dgImportar.Rows(e.RowIndex).Cells(2).Value & "' AND Estatus=1")
                dgImportar.Rows(e.RowIndex).Cells(3).Value = BuscarDblDatoCitra("SELECT Importe01 FROM Autobuses WHERE cCodigoC01='" & dgImportar.Rows(e.RowIndex).Cells(1).Value & "'")
                dgImportar.Rows(e.RowIndex).Cells(4).Value = 0
                dgImportar.Rows(e.RowIndex).Cells(7).Value = BuscarDblDatoCitra("SELECT Importe02 FROM Autobuses WHERE cCodigoC01='" & dgImportar.Rows(e.RowIndex).Cells(1).Value & "'")
            End If


            odometro = dgImportar.Rows(e.RowIndex).Cells(3).Value
            Final = dgImportar.Rows(e.RowIndex).Cells(4).Value
            Litros = dgImportar.Rows(e.RowIndex).Cells(5).Value
            rend = dgImportar.Rows(e.RowIndex).Cells(7).Value
            Recorridos = Final - odometro
            Rendimiento = Recorridos / Litros
            Diferencia = Rendimiento - rend

            dgImportar.Rows(e.RowIndex).Cells(6).Value = Recorridos
            dgImportar.Rows(e.RowIndex).Cells(7).Value = rend
            dgImportar.Rows(e.RowIndex).Cells(8).Value = Rendimiento
            dgImportar.Rows(e.RowIndex).Cells(9).Value = Diferencia




        Catch ex As Exception
            '.... Accion en caso de excepcion
        End Try
    End Sub



    Private Sub ToolStripButton5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)



    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub

    Private Sub ToolStripButton5_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton5.Click
        Importar()
        'dgImportar.Rows.Clear()
    End Sub

    Private Sub BuscarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub GuardarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GuardarToolStripMenuItem.Click
        Importar()

    End Sub

    Private Sub EliminarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EliminarToolStripMenuItem.Click
        frmDieselBorrar.ShowDialog()
    End Sub

    Private Sub NuevaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NuevaToolStripMenuItem.Click
        dgImportar.Rows.Clear()

    End Sub

    Private Sub RegresarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegresarToolStripMenuItem.Click
        Close()

    End Sub

    Private Sub AbastecimientosToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AbastecimientosToolStripMenuItem.Click
        frmRAbasDiesel.ShowDialog()
    End Sub

    Private Sub RendimientosToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RendimientosToolStripMenuItem.Click
        frmRDieselKms.ShowDialog()
    End Sub
    Sub CerrarDia()
        'Verifica que todas las rutas tengan asignado el concepto de Ventas
        Dim Rutas As Integer = BuscarIntDatoCitra("Select count(*) FROM Rutas")
        Dim RutasAsignadas As Integer = BuscarIntDatoCitra("Select count(*) from Conceptos where Tipo='Diesel'")
        Dim Faltan As Integer = Rutas - RutasAsignadas
        If Faltan > 0 Then
            MsgBox("No se puede cerrar el dia porque faltan " & Faltan & " rutas por asignar en Rutas > Conceptos", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        'Verifica que todas las rutas tengan asignado el cliente para envios
        Dim RutasConClientes As Integer = BuscarIntDatoCitra("Select count(*) from Rutas where Texto01<>''")
        Dim FaltanClientes As Integer = Rutas - RutasConClientes
        If FaltanClientes > 0 Then
            MsgBox("No se puede cerrar el dia poorque faltan " & FaltanClientes & " clientes para envio por asignar en el Catálogo de Rutas", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        'Carga Libreria
        lblStatus.Text = "Cargando librerias..."
        Application.DoEvents()


        Dim cCodigoDie As String = "2DIE"

        Dim RutaDLL As String = ""

        Try

            Dim key As Microsoft.Win32.RegistryKey
            key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(gsRegPath)
            RutaDLL = CType(key.GetValue("RutaDLL"), String)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
            MsgBox("Se interrumpio el proceso", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        If Usa_AdminPAQ = True Then
            'ENTONCES SE USA EL SDK DE ADMINPAQ
            lblStatus.Text = "Cargando librerias..."
            Application.DoEvents()

            'Carga SDK
            Dim lError As Integer
            lblStatus.Text = "Cargando SDK de Adminpaq..."
            Application.DoEvents()

            Try
                Directory.SetCurrentDirectory(RutaDLL)
                lError = SDK_AdminPAQ.fInicializaSDK
                If lError <> 0 Then
                    MensajeError(lError)
                    End
                End If
            Catch ex As Exception
                lblStatus.Text = ex.Message
                MsgBox("No se pudo cargar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End Try

            'Abre Empresa de AdminPAQ
            lblStatus.Text = "Abriendo empresa de AdminPAQ..."
            Application.DoEvents()

            Try
                lError = SDK_AdminPAQ.fAbreEmpresa(Comercial)
                If lError <> 0 Then
                    MensajeError(lError)
                End If
            Catch ex As Exception
                MsgBox("No se pudo abrir la empresa de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End Try

        Else
            'ENTONCES SE USA EL DE COMERCIAL
            'Carga Libreria
            lblStatus.Text = "Cargando SDK de ContPAQ I Comercial"
            Application.DoEvents()
            Dim lRet As Long
            lRet = fSDK_Inicializa(Comercial)
            If lRet <> 0 Then
                MsgBox("No pudo ser iniciado el SDK de Comercial", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If

        End If



        Dim Dias As Integer = DateDiff(DateInterval.Day, datFecha.Value, datFecha2.Value)
        FilePath = Application.StartupPath & "\Diesel_Bitacora" & Now.Year & Now.Month & Now.Day & "_" & Now.Hour & Now.Minute & ".txt"

        strStreamW = File.OpenWrite(FilePath)
        strStreamWriter = New StreamWriter(strStreamW, _
                            System.Text.Encoding.UTF8)

        If Dias < 0 Then
            MsgBox("El rango de fechas es incorrecto", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        ElseIf Dias = 0 Then
            If DiaTrabajadoDiesel(datFecha.Value) = True Then
                MsgBox("El dia " & datFecha.Text & " ya ha sido archivado", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            'AQUI TAMBIEN
            Comienza(cCodigoDie)
        ElseIf Dias > 0 Then
            datFecha.Value = datFecha.Value.AddDays(-1)
            Application.DoEvents()
            For i = 0 To Dias
                datFecha.Value = datFecha.Value.AddDays(1)
                Application.DoEvents()
                If Not (DiaTrabajadoDiesel(datFecha.Value) = True) Then
                    'AQUI SUCEDE TODO
                    Comienza(cCodigoDie)
                End If
            Next
        End If

        MsgBox("Los dias se han cerrado correctamente", MsgBoxStyle.Information, "Aviso")

        strStreamWriter.Close()
        strStreamW.Close()





        'Cierra el SDK
        '        lblStatus.Text = "Cerrando SDK de AdminPAQ..."
        '        Application.DoEvents()

        Try
            SDK_AdminPAQ.fCierraEmpresa()
            SDK_AdminPAQ.fTerminaSDK()
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            'MsgBox("No se pudo cerrar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        lblStatus.Text = ""
        Application.DoEvents()
        Close()


    End Sub
    Private strStreamW As Stream
    Private strStreamWriter As StreamWriter
    Private FilePath As String
    Sub Comienza(ByVal CodigoProducto As String)

        'Genera Informacion para Envio
        Dim cnconn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Dim FechaCarga As String = datFecha.Value.ToString("MM/dd/yyyy")

        Try
            lblStatus.Text = "Buscando abastecimientos de diesel..."
            Application.DoEvents()

            cnconn.ConnectionString = CitraConnection
            strSql = "Select IdRuta,SUM(Litros) as Litros FROM Diesel WHERE convert(varchar,fecha,101)='" & FechaCarga & "' GROUP BY IdRuta"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Dim CodigoCliente As String = 0
            Dim CodigoRuta As String = ""
            Dim CodigoConepto As String = ""
            Dim Fecha As Date = Now()
            Dim AdminPAQ As New clsAdminPAQ
            Dim Dia As New clsDiasTrabajoDiesel
            Dim Cont As Integer = 0
            Dim NombreRuta As String = ""

            Dim IdProducto As Integer = 0 ' BuscarIntDatoCitra("SELECT cIdProdu01 FROM MGW10005 WHERE cCodigoP01='" & cCodigoDie & "'")
            Dim IdAlmacen As Integer = 0
            Dim Costo As Double = 0

            Dim Resultado As String = ""

            Do While rdBuscar.Read()
                'Primero busca cliente para Envios
                CodigoProducto = BuscarStrDatoCitra("SELECT Texto03 FROM Rutas WHERE IdRuta=" & rdBuscar("IdRuta"))
                IdProducto = BuscarIntDatoCitra("SELECT cIdProdu01 FROM MGW10005 WHERE cCodigoP01='" & CodigoProducto & "'")
                CodigoCliente = BuscarStrDatoCitra("Rutas", "Texto01", "IdRuta=" & rdBuscar("IdRuta"))
                CodigoRuta = BuscarStrDatoCitra("Rutas", "cCodigoV01", "IdRuta=" & rdBuscar("IdRuta"))
                lblStatus.Text = "Enviando registros a AdminPAQ..." & CodigoRuta
                Application.DoEvents()
                'Busca Concepto
                CodigoConepto = BuscarStrDatoCitra("Conceptos", "CodigoConcepto", "CodigoRuta='" & CodigoRuta & "' AND Tipo='Diesel'")
                Fecha = datFecha.Value
                NombreRuta = BuscarStrDatoCitra("Rutas", "Nombre", "IdRuta=" & rdBuscar("IdRuta"))
                IdAlmacen = BuscarIntDatoCitra("SELECT cIdAlmacen FROM MGW10003 WHERE cCodigoA01='" & BuscarStrDatoCitra("Rutas", "Texto02", "IdRuta=" & rdBuscar("IdRuta")) & "'")
                Costo = BuscarDblDatoCitra("SELECT cUltimoC01 FROM MGW10017 WHERE cIdAlmacen=" & IdAlmacen & " AND cIdProdu01=" & IdProducto & " ORDER BY cIdCostoH DESC")

                If Usa_AdminPAQ = True Then
                    Resultado = AdminPAQ.EnviaAdminPAQ(Fecha, CodigoConepto, BuscarIntDatoComercial("SELECT CNOFOLIO FROM MGW10006 WHERE CCODIGOC01='" & CodigoConepto & "'") + 1, "DIE", CodigoCliente, NombreRuta, "", CodigoProducto, BuscarStrDatoCitra("Rutas", "Texto02", "IdRuta=" & rdBuscar("IdRuta")), rdBuscar("Litros"), Costo)
                    If Mid(Resultado, 1, 2) = "NO" Then
                        strStreamWriter.WriteLine(Fecha & ":" & NombreRuta & ":" & Mid(Resultado, 3))
                    End If
                Else
                    ' Resultado = admComercial.EnviaComercial(Fecha, CodigoConepto, , "DIE", CodigoCliente, NombreRuta, "", CodigoProducto, BuscarStrDatoCitra("Rutas", "Texto02", "IdRuta=" & rdBuscar("IdRuta")), rdBuscar("Litros"), Costo, AdminPAQ_Comercial.TIPO_DOCUMENTO_COMERCIAL.Ventas)
                    If Mid(Resultado, 1, 2) = "NO" Then
                        strStreamWriter.WriteLine(Mid(Resultado, 3))
                    ElseIf Mid(Resultado, 1, 5) = "ERROR" Then
                        strStreamWriter.WriteLine(Mid(Resultado, "ERROR AL CREAR EL DOCUMENTO" & Mid(Resultado, 6)))
                    End If

                End If

                Cont = Cont + 1
            Loop

            If Cont = 0 Then
                MsgBox("No se encontraron registros para procesar", MsgBoxStyle.Exclamation, "Aviso")
            Else
                Dia.Dia = Fecha.Day
                Dia.Mes = Fecha.Month
                Dia.Anio = Fecha.Year
                Dia.Insertar()
                ' MsgBox("El dia se ha cerrado correctamente", MsgBoxStyle.Information, "Aviso")
                Bitacora.RegistraActividad("Cerró el diesel del dia " & datFecha.Text)
            End If


        Catch ex As SqlException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub

    Private Sub CerrarDiaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CerrarDiaToolStripMenuItem.Click
        If DiaTrabajadoDiesel(datFecha.Value) = True Then
            MsgBox("El dia " & datFecha.Text & " ya ha sido archivado", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        CerrarDia()

    End Sub
    Sub ReAbrir()
        'Pide contraseña de reactivacion
        frmPassword.ShowDialog()
        If frmPassword.txtPass.Text.Trim <> "Ausl4321DIESEL" Then
            MsgBox("La contraseña es incorrecta", MsgBoxStyle.Critical, "Aviso")
            Exit Sub
        End If

        'Carga Libreria
        lblStatus.Text = "Cargando librerias..."
        Application.DoEvents()

        Dim RutaDLL As String = ""
        Dim cCodigoBol As String = ""
        Dim cCodigoASE As String = ""

        Try

            Dim key As Microsoft.Win32.RegistryKey
            key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(gsRegPath)
            RutaDLL = CType(key.GetValue("RutaDLL"), String)
            cCodigoBol = CType(key.GetValue("cCodigoBol"), String)
            cCodigoASE = CType(key.GetValue("cCodigoASE"), String)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
            MsgBox("Se interrumpio el proceso", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        'Carga SDK
        Dim lError As Integer
        lblStatus.Text = "Cargando SDK de Adminpaq..."
        Application.DoEvents()

        Try
            Directory.SetCurrentDirectory(RutaDLL)
            lError = SDK_AdminPAQ.fInicializaSDK
            If lError <> 0 Then
                MensajeError(lError)
                End
            End If
        Catch ex As Exception
            MsgBox("No se pudo cargar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End Try

        'Abre Empresa de AdminPAQ
        lblStatus.Text = "Abriendo empresa de AdminPAQ..."
        Application.DoEvents()

        Try
            lError = SDK_AdminPAQ.fAbreEmpresa(Comercial)
            If lError <> 0 Then
                MensajeError(lError)
            End If
        Catch ex As Exception
            MsgBox("No se pudo abrir la empresa de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End Try

        Continuar = False
        ImportaTablas(1)
        Timer1.Enabled = True
        Application.DoEvents()


        Do While Continuar = False
            lblStatus.Text = "Espere mientras se actualizan las tablas..."
            Application.DoEvents()
        Loop

        'Primero trae los codigos de Ruta
        'Variables para busqueda de rutas
        Dim cnConnCitra As New SqlConnection
        Dim strSqlCitra As String = ""
        Dim cmdBuscarCitra As New SqlCommand
        Dim rdBuscarCitra As SqlDataReader
        Dim Concepto As String = ""
        Dim CadenaConceptos As String = ""
        Dim cIDConce01 As Integer = 0

        'variables para busqueda en AdminPAQ


        'Arma conceptos
        Try
            lblStatus.Text = "Buscando conceptos..."
            Application.DoEvents()

            cnConnCitra.ConnectionString = CitraConnection
            strSqlCitra = "Select CodigoConcepto FROM Conceptos Where Tipo='Diesel' GROUP BY CodigoConcepto"
            cmdBuscarCitra.Connection = cnConnCitra
            cmdBuscarCitra.CommandText = strSqlCitra
            cnConnCitra.Open()
            rdBuscarCitra = cmdBuscarCitra.ExecuteReader

            Do While rdBuscarCitra.Read()
                'Por Cada Ruta Buscar Registros en conceptos
                Concepto = rdBuscarCitra("CodigoConcepto")
                cIDConce01 = BuscarStrDatoCitra("MGW10006", "cidConce01", "cCodigoC01='" & Concepto & "'")
                CadenaConceptos = CadenaConceptos & "," & cIDConce01
            Loop
            CadenaConceptos = Mid(CadenaConceptos, 2)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnConnCitra.State = ConnectionState.Open Then
                cnConnCitra.Close()
            End If
        End Try

        Dim FechaLiquidacion As String = datFecha.Value.ToString("MM/dd/yyyy")
        Dim Fecha As Date = datFecha.Value
        Dim DT As New clsDiasTrabajoDiesel
        Dim cnconnAdmin As New SqlConnection
        Dim strSqlAdmin As String = ""
        Dim cmdBuscarAdmin As New SqlCommand
        Dim rdBuscarAdmin As SqlDataReader
        Dim cidDocum01 As Integer = 0

        Try
            lblStatus.Text = "Buscando registros en AdminPAQ..."
            Application.DoEvents()
            cnconnAdmin.ConnectionString = CitraConnection
            strSqlAdmin = "SELECT cIdDocum01 FROM MGW10008 WHERE CIDCONCE01 IN(" & CadenaConceptos & ") AND CONVERT(VARCHAR,CFECHA,101)='" & FechaLiquidacion & "'"
            cmdBuscarAdmin.Connection = cnconnAdmin
            cmdBuscarAdmin.CommandText = strSqlAdmin
            cnconnAdmin.Open()

            rdBuscarAdmin = cmdBuscarAdmin.ExecuteReader

            Do While rdBuscarAdmin.Read()
                lblStatus.Text = "Borrando Registros..."
                Application.DoEvents()

                cidDocum01 = rdBuscarAdmin("cidDocum01")
                BorraDocumento(cidDocum01)
            Loop

        Catch ex As OdbcException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconnAdmin.State = ConnectionState.Open Then
                cnconnAdmin.Close()
            End If
        End Try

        DT.Dia = Fecha.Day
        DT.Mes = Fecha.Month
        DT.Anio = Fecha.Year
        DT.Borrar()

        'Cierra el SDK
        lblStatus.Text = "Cerrando SDK de AdminPAQ..."
        Application.DoEvents()

        Try
            SDK_AdminPAQ.fCierraEmpresa()
            SDK_AdminPAQ.fTerminaSDK()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            MsgBox("No se pudo cerrar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        lblStatus.Text = ""
        Application.DoEvents()


        MsgBox("El dia se ha reactivado", MsgBoxStyle.Information, "Aviso")
        Bitacora.RegistraActividad("Re-abrió la Liquidacion del dia " & datFecha.Text)
    End Sub
    Sub BorraDocumento(ByVal IDDocto As Integer)
        Dim lRet As Long

        lRet = SDK_AdminPAQ.fBuscarIdDocumento(IDDocto)
        If lRet <> 0 Then
            MensajeError(lRet)
        Else
            SDK_AdminPAQ.fBorraDocumento()
        End If
    End Sub
    Private Sub ReAbrirDiaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReAbrirDiaToolStripMenuItem.Click
        ReAbrir()

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        milisegundo += 1
        If milisegundo = 9 Then
            milisegundo = 0
            segundo += 1

            If segundo = 5 Then
                Application.DoEvents()
                If BuscarIntDatoCitra("SELECT Resultado FROM EstadoImportacion WHERE IdImportacion=1") = 0 Then
                    Continuar = True
                    Timer1.Enabled = False
                    Application.DoEvents()
                Else
                    segundo = 1
                End If
            End If
        End If

    End Sub

    Private Sub txtFile_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFile.TextChanged

    End Sub
End Class